﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace InSys.Documents
{
    public class AccessUserLoginInfo
    {
        private MapTestLinqDataContext db;

        public AccessUserLoginInfo()
        {
            try
            {
                db = new MapTestLinqDataContext();
                if (!db.DatabaseExists())
                    db.CreateDatabase();
            }
            catch (Exception)
            {
                
            }
        }

        /// <summary>
        /// Получить список отделов
        /// </summary>
        /// <returns></returns>
        public IEnumerable<department> getDepartments()
        {
            return from item in db.departments orderby item.name select item;
        }

        /// <summary>
        /// Получить список сотрудников отдела на указанную дату (на конец суток)
        /// </summary>
        /// <param name="db"></param>
        /// <param name="id_department"></param>
        /// <param name="actual_data"></param>
        /// <returns></returns>
        public IEnumerable<person> getListPersonsDepartment(int id_department, DateTime actual_date)
        {
            actual_date = new DateTime(actual_date.Year, actual_date.Month, actual_date.Day, 23, 59, 59);
            var queryPersons = from item in db.person_departments where item.date <= actual_date group item by item.id_person into itemGroup select new { id_person = itemGroup.Key, last_date=itemGroup.Max(o=>o.date)};
            var selPersonsInDepartment = from a in db.person_departments.Where(o => o.id_department == id_department) from b in queryPersons where a.id_person == b.id_person & a.date == b.last_date select a.id_person;
            IEnumerable<person> query = from a in db.persons from sel in selPersonsInDepartment where a.id_person == sel orderby a.name select a;
            return query;
        }

        public string GetUserNameById(int id)
        {
            return (from item_person in db.persons where item_person.id_person == id select item_person.name).FirstOrDefault();
        }

        public string GetDepartmentNameById(int id)
        {
            return (from item_department in db.departments where item_department.id_department == id select item_department.name).FirstOrDefault();
        }
    }
}
